package com.kqkj.dyrule.marketing.utils;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;

public class ExcelUtils {

    public static void main(String[] args) throws IOException {
        ExcelUtils obj = new ExcelUtils();
        ArrayList<String> strings = obj.readExcel("C:\\Users\\K\\Desktop", "ratings.xlsx");

        System.out.println("数据打印出来");

        FileOutputStream fileOutputStream = null;
        File file = new File("C:\\Users\\K\\Desktop\\add.txt");
        if(!file.exists()){
            file.createNewFile();
        }
        fileOutputStream = new FileOutputStream(file);
        for (String string : strings) {
            System.out.println(string);
            String newLine = System.getProperty("line.separator");
            fileOutputStream.write(string.getBytes("utf-8"));
            fileOutputStream.write(newLine.getBytes());
        }

        fileOutputStream.flush();
        fileOutputStream.close();




    }

    public static ArrayList<String> readExcel(String dir, String file) {
        ArrayList<String> alist = new ArrayList<>();
        if (file.endsWith(".xlsx")) {// 根据表末尾扩展名判断使用哪个方法读取
            try {
                System.out.println("进入此方法");
                XSSFWorkbook book = new XSSFWorkbook(new File(dir + File.separator + file));
                int sheetNum = book.getNumberOfSheets();
                System.out.println("该excel文件中总共有：" + sheetNum + "个sheet");
                //遍历工作簿中的所有数据
                for (int i = 2; i < 3; i++) {
                    //读取第i个工作表
                    System.out.println("读取第" + (i + 1) + "个sheet");
                    XSSFSheet sheet = book.getSheetAt(i);
                    String sheetName = sheet.getSheetName();
                    System.out.println("sheet名字为" + sheetName);
                    //获取最后一行的num，即总行数。此处从0开始
                    int maxRow = sheet.getLastRowNum();
                    for (int row = 0; row <= maxRow; row++) {
                        //获取最后单元格num，即总单元格数 ***注意：此处从1开始计数***
                        int maxRol = sheet.getRow(row).getLastCellNum();
                        //System.out.println("--------第" + row + "行的数据如下--------");
                        //for (int rol = 0; rol < maxRol; rol++) {
                            //System.out.print(sheet.getRow(row).getCell(rol) + "  ");
                        //alist.add("insert into depart_code values ('"+sheet.getRow(row).getCell(0)+"','"+sheet.getRow(row).getCell(1)+"','"+sheet.getRow(row).getCell(2)+"','"+sheet.getRow(row).getCell(3)+"','"+sheetName+"');");
                        alist.add(sheet.getRow(row).getCell(0)+"||"+sheet.getRow(row).getCell(1));
                        //}
                    }
                }
                book.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return alist;
    }
}





